package cc.shacocloud.kotlin.tools.jdbc

import cc.shacocloud.kotlin.tools.Slf4j
import cc.shacocloud.kotlin.tools.Slf4j.Companion.log
import cc.shacocloud.kotlin.tools.jdbc.impl.ColumnMapRowMapper
import cc.shacocloud.kotlin.tools.jdbc.impl.RowMapperResultSetExtractor
import cc.shacocloud.kotlin.tools.jdbc.impl.SingleRowMapperResultSetExtractor
import java.io.StringReader
import java.io.StringWriter
import java.math.BigDecimal
import java.sql.*
import java.util.*
import java.util.Date

/**
 * jdbc 操作模板
 * @author 思追(shaco)
 */
@Slf4j
class JdbcTemplate(

    private val connection: Connection,

    /**
     * 如果此变量设置为非负值，则它将用于设置
     *
     * 用于查询处理的语句的 fetchSize 属性。
     */
    private val fetchSize: Int = -1,

    /**
     * 如果此变量设置为非负值，则它将用于设置
     *
     * 用于查询处理的语句的 maxRows 属性。
     */
    private val maxRows: Int = -1,

    /**
     * 如果此变量设置为非负值，则它将用于设置
     *
     * 用于查询处理的语句的 queryTimeout 属性。
     */
    private val queryTimeout: Int = -1,

    /**
     * 如果此变量为 'false'，我们将在 SQL 警告上引发异常
     */
    private val ignoreWarnings: Boolean = true
) {

    companion object {

        /**
         * 指示未知（或未指定）SQL 类型的常量
         */
        const val TYPE_UNKNOWN: Int = Int.MIN_VALUE
    }

    /**
     * 执行查询语句
     */
    fun <T> query(sql: String, args: Array<Any?>? = null, resultSetExtractor: ResultSetExtractor<T>): T {
        return execute(PreparedStatementCreator(sql)) { statement ->
            val ps = statement as PreparedStatement

            setValues(ps, args)

            // 执行查询
            val resultSet = ps.executeQuery()
            resultSet.use { resultSetExtractor.extractData(resultSet) }
        }
    }

    /**
     * 执行查询语句，返回自定义封装结果对象
     */
    fun <T> queryForRowsMapper(sql: String, args: Array<Any?>? = null, rowsMapper: RowMapper<T>): T {
        return query(sql, args, SingleRowMapperResultSetExtractor(rowsMapper))
    }

    /**
     * 执行查询语句，返回单个对象
     */
    fun query(sql: String, args: Array<Any?>? = null): Map<String, Any?> {
        return query(sql, args, SingleRowMapperResultSetExtractor(ColumnMapRowMapper()))
    }

    /**
     * 执行查询语句，返回自定义封装结果集合
     */
    fun <T> queryListForRowsMapper(sql: String, args: Array<Any?>? = null, rowsMapper: RowMapper<T>): List<T> {
        return query(sql, args, RowMapperResultSetExtractor(rowsMapper))
    }

    /**
     * 执行查询语句，返回集合
     */
    fun queryList(sql: String, args: Array<Any?>? = null): List<Map<String, Any?>> {
        return query(sql, args, RowMapperResultSetExtractor(ColumnMapRowMapper()))
    }

    /**
     * 执行查询语句
     */
    fun update(sql: String, args: Array<Any?>? = null): Int {
        return execute(PreparedStatementCreator(sql)) { statement ->
            val ps = statement as PreparedStatement

            setValues(ps, args)

            // 执行查询
            ps.executeUpdate()
        }
    }

    /**
     * 插入数据并且返回自增键
     */
    fun insertAndGenerateKey(sql: String, args: Array<Any?>? = null): GenerateKeyResult {
        return execute(PreparedStatementGenerateKeyCreator(sql)) { statement ->
            val ps = statement as PreparedStatement

            setValues(ps, args)

            // 执行查询
            val rows = ps.executeUpdate()

            val extractData = ps.generatedKeys?.use {
                val resultSetExtractor = RowMapperResultSetExtractor(ColumnMapRowMapper())
                resultSetExtractor.extractData(it)
            }

            GenerateKeyResult(rows, extractData ?: listOf())
        }
    }

    /**
     * 执行单个 SQL 执行，通常是 DDL 语句。
     */
    fun execute(sql: String) {
        execute(CreateStatementCreator()) { statement -> statement.execute(sql) }
    }

    /**
     * 执行语句
     */
    fun <T> execute(statementCreator: StatementCreator, callback: StatementCallback<T>): T {
        val statement = statementCreator.createStatement(connection)
        statement.use {
            applyStatementSettings(statement)
            val result = callback.doInStatement(statement)
            handleWarnings(statement)
            return result
        }
    }

    // --------  私有

    /**
     * 设置参数
     */
    private fun setValues(ps: PreparedStatement, args: Array<Any?>?) {
        if (!args.isNullOrEmpty()) {
            args.forEachIndexed { index, arg ->
                val paramIndex = index + 1
                if (arg == null) {
                    setNull(ps, paramIndex, TYPE_UNKNOWN)
                } else {
                    setValue(ps, paramIndex, TYPE_UNKNOWN, null, arg)
                }
            }
        }
    }

    /**
     * 设置值
     */
    private fun setValue(
        ps: PreparedStatement,
        paramIndex: Int,
        sqlType: Int,
        scale: Int?,
        inValue: Any
    ) {

        if (sqlType == Types.VARCHAR || sqlType == Types.LONGVARCHAR) {
            ps.setString(paramIndex, inValue.toString())
        } else if (sqlType == Types.NVARCHAR || sqlType == Types.LONGNVARCHAR) {
            ps.setNString(paramIndex, inValue.toString())
        } else if ((sqlType == Types.CLOB || sqlType == Types.NCLOB) && isStringValue(inValue.javaClass)) {
            val strVal = inValue.toString()
            if (strVal.length > 4000) {
                // 对于较旧的 Oracle 驱动程序是必需的，尤其是在针对 Oracle 10 数据库运行时。
                // 也应该可以很好地对抗其他驱动程序/数据库，因为它使用标准的 JDBC 4.0 API。
                if (sqlType == Types.NCLOB) {
                    ps.setNClob(paramIndex, StringReader(strVal), strVal.length.toLong())
                } else {
                    ps.setClob(paramIndex, StringReader(strVal), strVal.length.toLong())
                }
            } else {
                // 回退：setString 或 setNString 绑定
                if (sqlType == Types.NCLOB) {
                    ps.setNString(paramIndex, strVal)
                } else {
                    ps.setString(paramIndex, strVal)
                }
            }
        } else if (sqlType == Types.DECIMAL || sqlType == Types.NUMERIC) {
            if (inValue is BigDecimal) {
                ps.setBigDecimal(paramIndex, inValue)
            } else if (scale != null) {
                ps.setObject(paramIndex, inValue, sqlType, scale)
            } else {
                ps.setObject(paramIndex, inValue, sqlType)
            }
        } else if (sqlType == Types.BOOLEAN) {
            if (inValue is Boolean) {
                ps.setBoolean(paramIndex, inValue)
            } else {
                ps.setObject(paramIndex, inValue, Types.BOOLEAN)
            }
        } else if (sqlType == Types.DATE) {
            if (inValue is Date) {
                if (inValue is java.sql.Date) {
                    ps.setDate(paramIndex, inValue)
                } else {
                    ps.setDate(paramIndex, java.sql.Date(inValue.time))
                }
            } else if (inValue is Calendar) {
                val cal = inValue
                ps.setDate(paramIndex, java.sql.Date(cal.time.time), cal)
            } else {
                ps.setObject(paramIndex, inValue, Types.DATE)
            }
        } else if (sqlType == Types.TIME) {
            if (inValue is Date) {
                if (inValue is Time) {
                    ps.setTime(paramIndex, inValue)
                } else {
                    ps.setTime(paramIndex, Time(inValue.time))
                }
            } else if (inValue is Calendar) {
                val cal = inValue
                ps.setTime(paramIndex, Time(cal.time.time), cal)
            } else {
                ps.setObject(paramIndex, inValue, Types.TIME)
            }
        } else if (sqlType == Types.TIMESTAMP) {
            if (inValue is Date) {
                if (inValue is Timestamp) {
                    ps.setTimestamp(paramIndex, inValue)
                } else {
                    ps.setTimestamp(paramIndex, Timestamp(inValue.time))
                }
            } else if (inValue is Calendar) {
                val cal = inValue
                ps.setTimestamp(paramIndex, Timestamp(cal.time.time), cal)
            } else {
                ps.setObject(paramIndex, inValue, Types.TIMESTAMP)
            }
        } else if (sqlType == TYPE_UNKNOWN || (sqlType == Types.OTHER && "Oracle" == ps.connection.metaData.databaseProductName)) {
            if (isStringValue(inValue.javaClass)) {
                ps.setString(paramIndex, inValue.toString())
            } else if (isDateValue(inValue.javaClass)) {
                ps.setTimestamp(paramIndex, Timestamp((inValue as Date).time))
            } else if (inValue is Calendar) {
                val cal = inValue
                ps.setTimestamp(paramIndex, Timestamp(cal.time.time), cal)
            } else {
                // 回退到未指定 SQL 类型的泛型 setObject 调用。
                ps.setObject(paramIndex, inValue)
            }
        } else {
            // 回退到指定了 SQL 类型的通用 setObject 调用。
            ps.setObject(paramIndex, inValue, sqlType)
        }
    }

    /**
     * 检查给定值是否为“java.util.Date” （但不是特定于 JDBC 的子类之一）。
     */
    private fun isDateValue(inValueType: Class<*>): Boolean {
        return Date::class.java.isAssignableFrom(inValueType) &&
                !(java.sql.Date::class.java.isAssignableFrom(inValueType) ||
                        Time::class.java.isAssignableFrom(inValueType) ||
                        Timestamp::class.java.isAssignableFrom(inValueType))
    }

    /**
     * 检查给定值是否可以被视为 String 值。
     */
    private fun isStringValue(inValueType: Class<*>): Boolean {
        // 将任何 CharSequence（包括 StringBuffer 和 StringBuilder）视为 String。
        return (CharSequence::class.java.isAssignableFrom(inValueType)
                || StringWriter::class.java.isAssignableFrom(inValueType))
    }

    /**
     * 将指定的 PreparedStatement 参数设置为 null，尊重数据库的特定特性。
     */
    private fun setNull(ps: PreparedStatement, paramIndex: Int, sqlType: Int) {
        if (sqlType == TYPE_UNKNOWN || (sqlType == Types.OTHER)) {
            var useSetObject = false
            var sqlTypeToUse: Int? = null

            try {
                sqlTypeToUse = ps.parameterMetaData.getParameterType(paramIndex)
            } catch (ex: SQLException) {
                if (log.isDebugEnabled) {
                    log.debug("JDBC getParameterType 调用失败 - 改用回退方法: $ex")
                }
            }

            if (sqlTypeToUse == null) {
                // 继续进行特定于数据库的检查
                sqlTypeToUse = Types.NULL
                val dbmd = ps.connection.metaData
                val jdbcDriverName = dbmd.driverName
                val databaseProductName = dbmd.databaseProductName
                if (databaseProductName.startsWith("Informix") ||
                    (jdbcDriverName.startsWith("Microsoft") && jdbcDriverName.contains("SQL Server"))
                ) {
                    // “Microsoft SQL Server JDBC 驱动程序 3.0”与“Microsoft JDBC Driver 4.0 for SQL Server”
                    useSetObject = true
                } else if (databaseProductName.startsWith("DB2") ||
                    jdbcDriverName.startsWith("jConnect") ||
                    jdbcDriverName.startsWith("SQLServer") ||
                    jdbcDriverName.startsWith("Apache Derby")
                ) {
                    sqlTypeToUse = Types.VARCHAR
                }
            }
            if (useSetObject) {
                ps.setObject(paramIndex, null)
            } else {
                ps.setNull(paramIndex, sqlTypeToUse)
            }
        } else {
            ps.setNull(paramIndex, sqlType)
        }
    }

    /**
     *如果我们没有忽略警告，则抛出 SQLWarningException， 则，在调试级别记录警告。
     */
    private fun handleWarnings(stmt: Statement) {
        if (ignoreWarnings) {
            if (log.isDebugEnabled) {
                var warningToLog = stmt.warnings
                while (warningToLog != null) {
                    log.debug("SQLWarning 可忽略: SQL 状态 '${warningToLog.sqlState}', 错误代码 '${warningToLog.errorCode}', 消息 [${warningToLog.message}]")
                    warningToLog = warningToLog.nextWarning
                }
            }
        } else {
            if (stmt.warnings != null) {
                throw SQLWarningException("SQL 警告异常，未忽略", stmt.warnings)
            }
        }
    }

    /**
     *准备给定的 JDBC 语句（或 PreparedStatement 或 CallableStatement），
     * 应用语句设置，如提取大小、最大行数和查询超时
     */
    private fun applyStatementSettings(stmt: Statement) {
        if (fetchSize != -1) {
            stmt.fetchSize = fetchSize
        }
        if (maxRows != -1) {
            stmt.maxRows = maxRows
        }
        if (queryTimeout != -1) {
            stmt.queryTimeout = queryTimeout
        }
    }

}

data class GenerateKeyResult(val rows: Int, val keys: List<Map<String, Any?>>)

class CreateStatementCreator : StatementCreator {

    override fun createStatement(con: Connection): Statement {
        return con.createStatement()
    }

}

class PreparedStatementGenerateKeyCreator(val sql: String) : StatementCreator {

    override fun createStatement(con: Connection): Statement {
        return con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)
    }

}

class PreparedStatementCreator(val sql: String) : StatementCreator {

    override fun createStatement(con: Connection): Statement {
        return con.prepareStatement(sql)
    }

}

fun interface StatementCreator {

    fun createStatement(con: Connection): Statement
}


fun interface StatementCallback<T> {

    fun doInStatement(stmt: Statement): T

}

fun interface ResultSetExtractor<T> {

    fun extractData(rs: ResultSet): T

}

fun interface RowMapper<T> {

    fun mapRow(rs: ResultSet, rowNum: Int): T
}